package com.example.demo.mapper;

import com.example.demo.model.TeacherApproval;
import org.apache.ibatis.annotations.*;
import java.util.List;

/**
 * 教师权限申请审批数据访问接口
 */
@Mapper
public interface TeacherApprovalMapper {
    
    @Insert("INSERT INTO teacher_approval(user_id, requested_role, approval_status, approval_comment) " +
            "VALUES(#{userId}, #{requestedRole}, #{approvalStatus}, #{approvalComment})")
    @Options(useGeneratedKeys = true, keyProperty = "approvalId")
    int insert(TeacherApproval approval);
    
    @Update("UPDATE teacher_approval SET approval_status = #{approvalStatus}, " +
            "approval_comment = #{approvalComment}, approver_id = #{approverId}, " +
            "approval_time = NOW() " +
            "WHERE approval_id = #{approvalId}")
    int update(TeacherApproval approval);
    
    @Select("SELECT ta.*, u.usercode, u.full_name, u.email, " +
            "JSON_UNQUOTE(JSON_EXTRACT(u.extra_info, '$.teacher_code')) as teacher_code, " +
            "JSON_UNQUOTE(JSON_EXTRACT(u.extra_info, '$.title')) as title, " +
            "o.name as org_name, " +
            "u2.full_name as approver_name " +
            "FROM teacher_approval ta " +
            "JOIN users u ON ta.user_id = u.user_id " +
            "JOIN organizations o ON u.org_id = o.org_id " +
            "LEFT JOIN users u2 ON ta.approver_id = u2.user_id " +
            "WHERE ta.approval_id = #{approvalId}")
    TeacherApproval findById(@Param("approvalId") Integer approvalId);
    
    @Select("SELECT ta.*, u.usercode, u.full_name, u.email, " +
            "JSON_UNQUOTE(JSON_EXTRACT(u.extra_info, '$.teacher_code')) as teacher_code, " +
            "JSON_UNQUOTE(JSON_EXTRACT(u.extra_info, '$.title')) as title, " +
            "o.name as org_name, " +
            "u2.full_name as approver_name " +
            "FROM teacher_approval ta " +
            "JOIN users u ON ta.user_id = u.user_id " +
            "JOIN organizations o ON u.org_id = o.org_id " +
            "LEFT JOIN users u2 ON ta.approver_id = u2.user_id " +
            "WHERE ta.user_id = #{userId}")
    List<TeacherApproval> findByUserId(@Param("userId") Integer userId);
    
    @Select("SELECT ta.*, u.usercode, u.full_name, u.email, " +
            "JSON_UNQUOTE(JSON_EXTRACT(u.extra_info, '$.teacher_code')) as teacher_code, " +
            "JSON_UNQUOTE(JSON_EXTRACT(u.extra_info, '$.title')) as title, " +
            "o.name as org_name, " +
            "u2.full_name as approver_name " +
            "FROM teacher_approval ta " +
            "JOIN users u ON ta.user_id = u.user_id " +
            "JOIN organizations o ON u.org_id = o.org_id " +
            "LEFT JOIN users u2 ON ta.approver_id = u2.user_id " +
            "WHERE ta.approval_status = #{status}")
    List<TeacherApproval> findByStatus(@Param("status") String status);
    
    @Select("SELECT ta.*, u.usercode, u.full_name, u.email, " +
            "JSON_UNQUOTE(JSON_EXTRACT(u.extra_info, '$.teacher_code')) as teacher_code, " +
            "JSON_UNQUOTE(JSON_EXTRACT(u.extra_info, '$.title')) as title, " +
            "o.name as org_name, " +
            "u2.full_name as approver_name " +
            "FROM teacher_approval ta " +
            "JOIN users u ON ta.user_id = u.user_id " +
            "JOIN organizations o ON u.org_id = o.org_id " +
            "LEFT JOIN users u2 ON ta.approver_id = u2.user_id " +
            "ORDER BY ta.request_time DESC")
    List<TeacherApproval> findAll();
    
    @Select("SELECT * FROM teacher_approval " +
            "WHERE user_id = #{userId} AND requested_role = #{requestedRole} " +
            "AND approval_status = 'PENDING'")
    TeacherApproval findPendingApproval(@Param("userId") Integer userId, @Param("requestedRole") String requestedRole);
} 